﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_notify_new_server]
#-- Purpose:		Send out a notification email of new servers discovered
#--	Last Update:	08/25/2014
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_notify_new_server]
(
	@days_back			int,
	@return_results		bit			= 0,
	@send_notification	bit			= 1
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@sSQL nvarchar(MAX), @subject nvarchar(255), @recipients varchar(MAX), @database_name sysname

--- Initialize Local Variables
SELECT		@database_name = DB_NAME(),
			@subject = 'Server Discovery since ' + CONVERT(VARCHAR(10), DATEADD(dd, (30 * -1), CONVERT(VARCHAR(10), GETDATE(), 101)), 101),
			@recipients = (SELECT cfg_value FROM [dbo].[tblMSX_configuration] WHERE cfg_key = 'AdminEmailAccount') + '@' + (SELECT cfg_value FROM [dbo].[tblMSX_configuration] WHERE cfg_key = 'AdminEmailDomainName'),
			@sSQL = 'SELECT		ssd_sqlserver_name_ro,
			ssd_type_ro,
			app.[app_name] sql_description, 
			sql_has_centraldba_db,
			sql_has_master_jobs,
			sql_backup_directory,
			ssd_pwrshl_script_dir,
			LTRIM(RTRIM(ISNULL(sql_edition_ro, '''') + '' '' + ISNULL(sql_product_version, ''''))) sql_version,
			ssd_is_vendor_managed_ro,
			ssd_sql_service,
			ssd_sql_start_mode,
			ssd_sql_state,
			ssd_agent_service,
			ssd_agent_start_mode,
			ssd_agent_state
FROM		[dbo].[tblMSX_server_discovery] ssd
LEFT JOIN	[dbo].[tblMSX_application] app ON ssd.[ssd_app_id] = app.[app_id]
LEFT JOIN	[dbo].[tblMSX_server_sql_info] ssql ON ssd.ssd_id = ssql.sql_ssd_id
WHERE		ssd_date_discovered >= ''' + CONVERT(varchar(10), DATEADD(dd, (@days_back * -1), CONVERT(varchar(10), GETDATE(), 101)), 101) + '''
			AND ssd_is_disabled = 0'

--- Return Results
IF @return_results = 1
	EXEC	(@sSQL)

--- Send Notification
IF @send_notification = 1 AND EXISTS (SELECT TOP 1 ssd_type_ro FROM [dbo].[tblMSX_server_discovery] WHERE ssd_is_disabled = 0 AND ssd_date_discovered >= DATEADD(dd, (@days_back * -1), CONVERT(varchar(10), GETDATE(), 101)))
  BEGIN
	EXEC	[msdb].[dbo].[sp_send_dbmail]	@subject = @subject,
											@recipients = @recipients,
											@body = 'Attached is a list of the servers that have been discovered.  Please review all of the columns for each server for appropriate values.',
											@execute_query_database = @database_name,
											@query = @sSQL,
											@attach_query_result_as_file = 1,
											@query_attachment_filename = 'server_discovery.csv'
  END

SET NOCOUNT OFF